﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.AnalysisServices.AdomdClient;

namespace MSBIHelper.Helper
{
    class DbHelper
    {
        private static string dbProviderName = "System.Data.SqlClient";
        private   string dbConnectionString = "data source={0};Integrated Security=True;";
        private DbConnection connection;
        private string connectstringtemplate = "Server={0};User Id={1};Password ={2}";
        public DbHelper()
        {
            this.connection = CreateConnection();
        }

        public DbHelper(string _connstr)
        {
            if (_connstr.Length > 0)
            {
                this.connection = CreateConnection(_connstr);
            }
        }
        public DbHelper(string _server,string _user,string _pwd)
        {
            if (_user.Length > 0 && _pwd.Length > 0)
            {
                this.connection =
                    CreateConnection(
                    string.Format(connectstringtemplate, _server, _user, _pwd)
                    );
            }
            else
            { 
                this.connection = CreateConnection(
                    string.Format(dbConnectionString,_server));
            }
        }

        public static DbConnection CreateConnection()
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);

            DbConnection dbconn = dbfactory.CreateConnection();

            dbconn.ConnectionString = "data source=(localhost);Integrated Security=True;";

            return dbconn;

        }

        public static DbConnection CreateConnection(string connectionString)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);

            DbConnection dbconn = dbfactory.CreateConnection();

            dbconn.ConnectionString = connectionString;

            return dbconn;

        }

        public DbCommand GetStoredProcCommand(string storedProcedure)
        {

            DbCommand dbCommand = connection.CreateCommand();

            dbCommand.CommandText = storedProcedure;

            dbCommand.CommandType = CommandType.StoredProcedure;

            return dbCommand;

        }

        public DbCommand GetSqlStringCommand(string sqlQuery)
        {

            DbCommand dbCommand = connection.CreateCommand();

            dbCommand.CommandText = sqlQuery;

            dbCommand.CommandType = CommandType.Text;

            return dbCommand;

        }

        public static DataTable ExecuteMdx(string connstr, string mdxScripts)
        {
            DataTable dt = new DataTable();
            using (AdomdConnection conn = new AdomdConnection(connstr))
            {
                if (conn.State.Equals(ConnectionState.Closed))
                    conn.Open();
                AdomdDataAdapter objDataAdapter = new AdomdDataAdapter(mdxScripts, conn);
                objDataAdapter.Fill(dt);
                conn.Close();
            }

            return dt;
        }

        #region Add Parameter

        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {

            foreach (DbParameter dbParameter in dbParameterCollection)
            {

                cmd.Parameters.Add(dbParameter);

            }

        }

        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {

            DbParameter dbParameter = cmd.CreateParameter();

            dbParameter.DbType = dbType;

            dbParameter.ParameterName = parameterName;

            dbParameter.Size = size;

            dbParameter.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(dbParameter);

        }

        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
        {

            DbParameter dbParameter = cmd.CreateParameter();

            dbParameter.DbType = dbType;

            dbParameter.ParameterName = parameterName;

            dbParameter.Value = value;

            dbParameter.Direction = ParameterDirection.Input;

            cmd.Parameters.Add(dbParameter);

        }

        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
        {

            DbParameter dbParameter = cmd.CreateParameter();

            dbParameter.DbType = dbType;

            dbParameter.ParameterName = parameterName;

            dbParameter.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add(dbParameter);

        }

        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {

            return cmd.Parameters[parameterName];

        }



        #endregion

        #region Execute

        public DataSet ExecuteDataSet(DbCommand cmd)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);

            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();

            dbDataAdapter.SelectCommand = cmd;

            DataSet ds = new DataSet();

            dbDataAdapter.Fill(ds);

            return ds;

        }

        public DataTable ExecuteDataTable(DbCommand cmd)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);

            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();

            dbDataAdapter.SelectCommand = cmd;

            DataTable dataTable = new DataTable();

            dbDataAdapter.Fill(dataTable);

            return dataTable;

        }

        public DataTable ExecuteDataTable(string sqlQuery)
        {
            DbCommand dbc = GetSqlStringCommand(sqlQuery);
            return ExecuteDataTable(dbc);
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {

            cmd.Connection.Open();

            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            return reader;

        }

        public int ExecuteNonQuery(DbCommand cmd)
        {

            cmd.Connection.Open();

            int ret = cmd.ExecuteNonQuery();

            cmd.Connection.Close();

            return ret;

        }

        public int ExecuteNonQuery(string sqlQuery)
        {
            DbCommand dbc = GetSqlStringCommand(sqlQuery);
            return ExecuteNonQuery(dbc);
        }

        public object ExecuteScalar(DbCommand cmd)
        {

            cmd.Connection.Open();

            object ret = cmd.ExecuteScalar();

            cmd.Connection.Close();

            return ret;

        }

        #endregion

    }
}